<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<HTML
><HEAD
><TITLE
>System Columns</TITLE
><META
NAME="GENERATOR"
CONTENT="Modular DocBook HTML Stylesheet Version 1.79"><LINK
REV="MADE"
HREF="mailto:pgsql-docs@postgresql.org"><LINK
REL="HOME"
TITLE="PostgreSQL 9.1.2 Documentation"
HREF="index.html"><LINK
REL="UP"
TITLE="Data Definition"
HREF="ddl.html"><LINK
REL="PREVIOUS"
TITLE="Constraints"
HREF="ddl-constraints.html"><LINK
REL="NEXT"
TITLE="Modifying Tables"
HREF="ddl-alter.html"><LINK
REL="STYLESHEET"
TYPE="text/css"
HREF="stylesheet.css"><META
HTTP-EQUIV="Content-Type"
CONTENT="text/html; charset=ISO-8859-1"><META
NAME="creation"
CONTENT="2011-12-01T22:07:59"></HEAD
><BODY
CLASS="SECT1"
><DIV
CLASS="NAVHEADER"
><TABLE
SUMMARY="Header navigation table"
WIDTH="100%"
BORDER="0"
CELLPADDING="0"
CELLSPACING="0"
><TR
><TH
COLSPAN="5"
ALIGN="center"
VALIGN="bottom"
><A
HREF="index.html"
>PostgreSQL 9.1.2 Documentation</A
></TH
></TR
><TR
><TD
WIDTH="10%"
ALIGN="left"
VALIGN="top"
><A
TITLE="Constraints"
HREF="ddl-constraints.html"
ACCESSKEY="P"
>Prev</A
></TD
><TD
WIDTH="10%"
ALIGN="left"
VALIGN="top"
><A
HREF="ddl.html"
ACCESSKEY="U"
>Up</A
></TD
><TD
WIDTH="60%"
ALIGN="center"
VALIGN="bottom"
>Chapter 5. Data Definition</TD
><TD
WIDTH="20%"
ALIGN="right"
VALIGN="top"
><A
TITLE="Modifying Tables"
HREF="ddl-alter.html"
ACCESSKEY="N"
>Next</A
></TD
></TR
></TABLE
><HR
ALIGN="LEFT"
WIDTH="100%"></DIV
><DIV
CLASS="SECT1"
><H1
CLASS="SECT1"
><A
NAME="DDL-SYSTEM-COLUMNS"
>5.4. System Columns</A
></H1
><P
>   Every table has several <I
CLASS="FIRSTTERM"
>system columns</I
> that are
   implicitly defined by the system.  Therefore, these names cannot be
   used as names of user-defined columns.  (Note that these
   restrictions are separate from whether the name is a key word or
   not; quoting a name will not allow you to escape these
   restrictions.)  You do not really need to be concerned about these
   columns; just know they exist.
  </P
><P
></P
><DIV
CLASS="VARIABLELIST"
><DL
><DT
><TT
CLASS="STRUCTFIELD"
>oid</TT
></DT
><DD
><P
>      
      The object identifier (object ID) of a row. This column is only
      present if the table was created using <TT
CLASS="LITERAL"
>WITH
      OIDS</TT
>, or if the <A
HREF="runtime-config-compatible.html#GUC-DEFAULT-WITH-OIDS"
>default_with_oids</A
>
      configuration variable was set at the time. This column is of type
      <TT
CLASS="TYPE"
>oid</TT
> (same name as the column); see <A
HREF="datatype-oid.html"
>Section 8.16</A
> for more information about the type.
     </P
></DD
><DT
><TT
CLASS="STRUCTFIELD"
>tableoid</TT
></DT
><DD
><P
>      The OID of the table containing this row.  This column is
      particularly handy for queries that select from inheritance
      hierarchies (see <A
HREF="ddl-inherit.html"
>Section 5.8</A
>), since without it,
      it's difficult to tell which individual table a row came from.  The
      <TT
CLASS="STRUCTFIELD"
>tableoid</TT
> can be joined against the
      <TT
CLASS="STRUCTFIELD"
>oid</TT
> column of
      <TT
CLASS="STRUCTNAME"
>pg_class</TT
> to obtain the table name.
     </P
></DD
><DT
><TT
CLASS="STRUCTFIELD"
>xmin</TT
></DT
><DD
><P
>      The identity (transaction ID) of the inserting transaction for
      this row version.  (A row version is an individual state of a
      row; each update of a row creates a new row version for the same
      logical row.)
     </P
></DD
><DT
><TT
CLASS="STRUCTFIELD"
>cmin</TT
></DT
><DD
><P
>      The command identifier (starting at zero) within the inserting
      transaction.
     </P
></DD
><DT
><TT
CLASS="STRUCTFIELD"
>xmax</TT
></DT
><DD
><P
>      The identity (transaction ID) of the deleting transaction, or
      zero for an undeleted row version.  It is possible for this column to
      be nonzero in a visible row version. That usually indicates that the
      deleting transaction hasn't committed yet, or that an attempted
      deletion was rolled back.
     </P
></DD
><DT
><TT
CLASS="STRUCTFIELD"
>cmax</TT
></DT
><DD
><P
>      The command identifier within the deleting transaction, or zero.
     </P
></DD
><DT
><TT
CLASS="STRUCTFIELD"
>ctid</TT
></DT
><DD
><P
>      The physical location of the row version within its table.  Note that
      although the <TT
CLASS="STRUCTFIELD"
>ctid</TT
> can be used to
      locate the row version very quickly, a row's
      <TT
CLASS="STRUCTFIELD"
>ctid</TT
> will change if it is
      updated or moved by <TT
CLASS="COMMAND"
>VACUUM FULL</TT
>.  Therefore
      <TT
CLASS="STRUCTFIELD"
>ctid</TT
> is useless as a long-term row
      identifier.  The OID, or even better a user-defined serial
      number, should be used to identify logical rows.
     </P
></DD
></DL
></DIV
><P
>    OIDs are 32-bit quantities and are assigned from a single
    cluster-wide counter.  In a large or long-lived database, it is
    possible for the counter to wrap around.  Hence, it is bad
    practice to assume that OIDs are unique, unless you take steps to
    ensure that this is the case.  If you need to identify the rows in
    a table, using a sequence generator is strongly recommended.
    However, OIDs can be used as well, provided that a few additional
    precautions are taken:

    <P
></P
></P><UL
><LI
><P
>       A unique constraint should be created on the OID column of each
       table for which the OID will be used to identify rows.  When such
       a unique constraint (or unique index) exists, the system takes
       care not to generate an OID matching an already-existing row.
       (Of course, this is only possible if the table contains fewer
       than 2<SUP
>32</SUP
> (4 billion) rows, and in practice the
       table size had better be much less than that, or performance
       might suffer.)
      </P
></LI
><LI
><P
>       OIDs should never be assumed to be unique across tables; use
       the combination of <TT
CLASS="STRUCTFIELD"
>tableoid</TT
> and row OID if you
       need a database-wide identifier.
      </P
></LI
><LI
><P
>       Of course, the tables in question must be created <TT
CLASS="LITERAL"
>WITH
       OIDS</TT
>.  As of <SPAN
CLASS="PRODUCTNAME"
>PostgreSQL</SPAN
> 8.1,
       <TT
CLASS="LITERAL"
>WITHOUT OIDS</TT
> is the default.
      </P
></LI
></UL
><P>
   </P
><P
>    Transaction identifiers are also 32-bit quantities.  In a
    long-lived database it is possible for transaction IDs to wrap
    around.  This is not a fatal problem given appropriate maintenance
    procedures; see <A
HREF="maintenance.html"
>Chapter 23</A
> for details.  It is
    unwise, however, to depend on the uniqueness of transaction IDs
    over the long term (more than one billion transactions).
   </P
><P
>    Command identifiers are also 32-bit quantities.  This creates a hard limit
    of 2<SUP
>32</SUP
> (4 billion) <ACRONYM
CLASS="ACRONYM"
>SQL</ACRONYM
> commands
    within a single transaction.  In practice this limit is not a
    problem &mdash; note that the limit is on the number of
    <ACRONYM
CLASS="ACRONYM"
>SQL</ACRONYM
> commands, not the number of rows processed.
    Also, as of <SPAN
CLASS="PRODUCTNAME"
>PostgreSQL</SPAN
> 8.3, only commands
    that actually modify the database contents will consume a command
    identifier.
   </P
></DIV
><DIV
CLASS="NAVFOOTER"
><HR
ALIGN="LEFT"
WIDTH="100%"><TABLE
SUMMARY="Footer navigation table"
WIDTH="100%"
BORDER="0"
CELLPADDING="0"
CELLSPACING="0"
><TR
><TD
WIDTH="33%"
ALIGN="left"
VALIGN="top"
><A
HREF="ddl-constraints.html"
ACCESSKEY="P"
>Prev</A
></TD
><TD
WIDTH="34%"
ALIGN="center"
VALIGN="top"
><A
HREF="index.html"
ACCESSKEY="H"
>Home</A
></TD
><TD
WIDTH="33%"
ALIGN="right"
VALIGN="top"
><A
HREF="ddl-alter.html"
ACCESSKEY="N"
>Next</A
></TD
></TR
><TR
><TD
WIDTH="33%"
ALIGN="left"
VALIGN="top"
>Constraints</TD
><TD
WIDTH="34%"
ALIGN="center"
VALIGN="top"
><A
HREF="ddl.html"
ACCESSKEY="U"
>Up</A
></TD
><TD
WIDTH="33%"
ALIGN="right"
VALIGN="top"
>Modifying Tables</TD
></TR
></TABLE
></DIV
></BODY
></HTML
>